#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys
import json
import db_conf
import cgi

con = None

class DateTimeEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, datetime.datetime):
            encoded_object = list(obj.timetuple())[0:6]
        else:
            encoded_object =json.JSONEncoder.default(self, obj)
        return encoded_object

try:
    config = db_conf.getConfig('metabvis.conf')
    con = psycopg2.connect(host=db_conf.getDBHost(config), database=db_conf.getDBName(config), port=db_conf.getDBPort(config), user=db_conf.getUser(config), password=db_conf.getPassword(config)) 
    qform = cgi.FieldStorage()
    eid = qform.getvalue('experiment_id')
    cur = con.cursor()
    select_experiments = "select e.experiment_id, e.experimenter, e.date, e.project_id, e.title, e.short_description, e.description, sg.sample_group_id, sg.name, sg.bio_sample_type_id, sg.strain_id, sg.description, sg.regex, sg.sample_group_id, o.organism_text, st.bio_sample_type_text, p.full_name FROM experiment e LEFT JOIN people p ON e.experimenter = p.user_id LEFT JOIN chromatogram_sample_group csg ON e.experiment_id = csg.experiment_id LEFT JOIN sample_group sg ON sg.sample_group_id = csg.sample_group_id LEFT JOIN strain s ON  sg.strain_id = s.strain_id LEFT JOIN organism o ON s.organism_id = o.organism_id LEFT JOIN bio_sample_type st ON sg.bio_sample_type_id = st.bio_sample_type_id where e.archived != 1 and e.experiment_id = " + eid + " GROUP BY e.experiment_id, e.date, e.short_description, st.bio_sample_type_text, o.organism_text, sg.name, sg.bio_sample_type_id, sg.strain_id, sg.regex, sg.sample_group_id, sg.description, sg.sample_group_id, p.full_name  ORDER BY e.experiment_id"

    #select_experiments = "select e.experiment_id, e.experimenter, e.date, e.project_id, e.title, e.short_description, e.description, sg.sample_group_id, sg.name, sg.bio_sample_type_id, sg.strain_id, sg.description, sg.regex, sg.sample_group_id, o.organism_text, st.bio_sample_type_text from experiment e, sample_group sg,  organism o, strain s, bio_sample_type st where e.experiment_id = sg.experiment_id  and sg.strain_id = s.strain_id and s.organism_id = o.organism_id and sg.bio_sample_type_id = st.bio_sample_type_id and e.experiment_id = " + eid + " GROUP BY e.experiment_id, e.date, e.short_description, st.bio_sample_type_text, o.organism_text, sg.name, sg.bio_sample_type_id, sg.strain_id, sg.regex, sg.sample_group_id, sg.description, sg.sample_group_id order by sg.sample_group_id" 
    #print select_experiments
    cur.execute(select_experiments)          
    rows = cur.fetchall()
    print "Content-type: text/html;charset=utf-8\r\n"
    pa = {}
    pa['aaData'] = []
    for row in rows:
        pi = []
        for r in range(len(row)):
            if (r == 2):
                datestr = str(row[r]).split(" ")[0]
                #ins = DateTimeEncoder(row[r])
                #datestr = "-".join(ins[0:3])
                #print datestr
                pi.append(datestr)
            else:    	
                pi.append(row[r])
        pa['aaData'].append(pi)
    sample_groups = {}
    select_groups = "select sample_group_id, name, st.organism_id, st.strain_id, description, regex, sample_type_id, is_global from sample_group sg, strain st where sg.strain_id = st.strain_id and experiment_id = " + eid + " or is_global = 't' order by sample_group_id";
    pa['aaSGroups'] = []
    cur.execute(select_groups)
    rows = cur.fetchall()
    for row in rows:
        pi = []
        for r in range(len(row)):
                if (r==0 and row[6] == False):
                    sample_groups[row[r]] = 1
        	pi.append(row[r])
        pa['aaSGroups'].append(pi)
    if (len(sample_groups.keys()) > 0):
        sg_in = str(sample_groups.keys())[1:-1] 
        pa["tracers"] = []
        select_tracers = "select distinct(tracer.tracer_name), relative_tracer_amount, sample_group, labelfree_group from sample_group_tracer LEFT OUTER JOIN ntfd_sample_groups on (sample_group_tracer.sample_group = ntfd_sample_groups.label_group) INNER JOIN tracer on (sample_group_tracer.tracer_id = tracer.tracer_id) where sample_group in (" + sg_in + ") order by sample_group asc"
        #select distinct(tracer_name), relative_tracer_amount, sample_group, labelfree_group from sample_group_tracer LEFT OUTER JOIN ntfd_sample_groups on (sample_group_tracer.sample_group = ntfd_sample_groups.label_group) where sample_group in (" + sg_in + ") order by sample_group asc"
        cur.execute(select_tracers)
        rows = cur.fetchall()
        for row in rows:
            pi = []
            for r in range(len(row)):
                pi.append(row[r])
            pa['tracers'].append(pi)

    pa['aaChroms'] = []
    #select c.chromatogram_id, csg.sample_group_id, csg.experiment_id, c.proprietary_path, sg.name from chromatogram_sample_group csg, sample_group sg, chromatogram c where csg.experiment_id = 53 and csg.sample_group_id = sg.sample_group_id and csg.chromatogram_id = c.chromatogram_id order by c.chromatogram_id;
    select_chroms = "select c.chromatogram_id, csg.sample_group_id, csg.experiment_id, c.proprietary_path, sg.name, sg.is_global, sg.bio_sample_type_id, s.strain_name from chromatogram_sample_group csg, sample_group sg, chromatogram c, strain s where csg.experiment_id = " + eid + " and csg.sample_group_id = sg.sample_group_id and csg.chromatogram_id = c.chromatogram_id and sg.strain_id = s.strain_id order by c.chromatogram_id"
    cur.execute(select_chroms)
    rows = cur.fetchall()
    for row in rows:
        pi = []
        for r in range(len(row)):
            if (r == 3):
                ppath = "/".join(row[r].split("/")[4:])
                pi.append(ppath)
            else:
                pi.append(row[r])
        pa['aaChroms'].append(pi)    
    print json.dumps(pa)
except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1)    
finally:
    
    if con:
        con.close()
